package com.fuful.service;

import com.fuful.domain.PType;
import com.fuful.domain.Product;
import com.fuful.tools.DBHelper;

import java.util.*;

public class ProductService {
    private String sql = "";

    public Product findByProductId(String pid){
        sql = "select p.pid,p.pcode,p.pname,pt.ptid,pt.ptname,p.pprice,p.addtime " +
                "from products p " +
                "INNER JOIN ptype pt " +
                "on p.ptid = pt.ptid where p.pid = "+ pid;
        List<Map<String,Object>> data = DBHelper.query(sql);
        if(!data.isEmpty()){
            Map<String,Object> row = data.get(0);
            Product product = new Product(
                    Integer.parseInt(row.get("pid").toString())
                    ,row.get("pcode").toString()
                    ,row.get("pname").toString()
                    ,new PType(Integer.parseInt(row.get("ptid").toString()),row.get("ptname").toString())
                    ,Float.parseFloat(row.get("pprice").toString())
                    ,row.get("addtime").toString());
            return product;
        }
        return null;
    }

    public List<Product> findByProductType(String ptid){
        sql = "select p.pid,p.pcode,p.pname,pt.ptid,pt.ptname,p.pprice,p.addtime " +
                "from products p " +
                "INNER JOIN ptype pt " +
                "on p.ptid = pt.ptid where pt.ptid="+ ptid +" order by p.addtime desc";
        List<Map<String,Object>> data = DBHelper.query(sql);
        List<Product> products = new ArrayList<>();
        for (Map<String,Object> row: data) {
            Product product = new Product(
                    Integer.parseInt(row.get("pid").toString())
                    ,row.get("pcode").toString()
                    ,row.get("pname").toString()
                    ,new PType(Integer.parseInt(row.get("ptid").toString()),row.get("ptname").toString())
                    ,Float.parseFloat(row.get("pprice").toString())
                    ,row.get("addtime").toString());
            products.add(product);
        }
        return products;
    }

    public List<Product> findByProductName(String productName){
        sql = "select p.pid,p.pcode,p.pname,pt.ptid,pt.ptname,p.pprice,p.addtime " +
                "from products p " +
                "INNER JOIN ptype pt " +
                "on p.ptid = pt.ptid where pname like '%"+ productName +"%' order by p.addtime desc";
        List<Map<String,Object>> data = DBHelper.query(sql);
        List<Product> products = new ArrayList<>();
        for (Map<String,Object> row: data) {
            Product product = new Product(
                    Integer.parseInt(row.get("pid").toString())
                    ,row.get("pcode").toString()
                    ,row.get("pname").toString()
                    ,new PType(Integer.parseInt(row.get("ptid").toString()),row.get("ptname").toString())
                    ,Float.parseFloat(row.get("pprice").toString())
                    ,row.get("addtime").toString());
            products.add(product);
        }
        return products;
    }

    public List<Product> findByAll(){
        sql = "select p.pid,p.pcode,p.pname,pt.ptid,pt.ptname,p.pprice,p.addtime " +
                "from products p " +
                "INNER JOIN ptype pt " +
                "on p.ptid = pt.ptid;";
        List<Map<String,Object>> data = DBHelper.query(sql);
        List<Product> products = new ArrayList<>();
        for (Map<String,Object> row: data) {
            Product product = new Product(
                    Integer.parseInt(row.get("pid").toString())
                    ,row.get("pcode").toString()
                    ,row.get("pname").toString()
                    ,new PType(Integer.parseInt(row.get("ptid").toString()),row.get("ptname").toString())
                    ,Float.parseFloat(row.get("pprice").toString())
                    ,row.get("addtime").toString());
            products.add(product);
        }
        return products;
    }

}
